from sqlalchemy import select
from sqlalchemy.orm import  Session
from user_models import User,UserProfile,UserAddress

from db_engine import engine_future as engine

class MySqlOrmTest(object):
    # 方式一：实例化Session类
    # from sqlalchemy.orm import  Session
    # with Session(engine) as session:
    #     session.add(user_obj)
    #     session.commit()

    # 方式二：使用工厂函数创建
    # from sqlalchemy.orm import  sessionmaker
    # Session = sessionmaker(engine)
    # with Session.begin() as session:
    #     session.add(user_obj)


    def __init__(self):
        # v2.0风格
        self.session = Session(bind=engine,future=True)

    def add_user(self):
        # 新增用户(一条)
        user_obj = User(
            username = 'wangli',
            password = '123456',
            real_name = '王丽'
        )
        self.session.add(user_obj)
        self.session.commit()

    def add_many_user(self):
        # 新增用户(多条)
        user_obj = User(
            username = 'wangli',
            password = '123456',
            real_name = '王丽'
        )
        user_list = []
        for i in range(10):
            user_list.append(
                User(
                    username='wangli_{}'.format(i),
                    password='123456',
                    real_name='王丽_{}'.format(i)
                )
            )
        self.session.add_all(user_list)
        self.session.commit()

    def add_user_and_profile_address(self):
#         新增用户表、用户详情表、用户地址表
#        用户表
        user_obj = User(
            username='test',
            password='123456',
            real_name='test'
        )
        # 用户详情表
        profile = UserProfile(user=user_obj)
#         用户地址表
        user_obj.addresses.append(
            UserAddress(user=user_obj,area='地址1')
        )
        user_obj.addresses.append(
            UserAddress(user=user_obj,area='地址2')
        )

        self.session.add(user_obj)
        self.session.add(profile)
        self.session.commit()
        # 如果出错自动回滚 不需要手写回滚事务


# 出现异常，回滚事务  也可以写出来（可选）  下面是实例
# with Session(engine) as session:
#     session.begin()
#     try:
#         session.add(user1)
#         session.add(user2)
#     except:
#         session.rollback()
#         raise
#     else:
#         session.commit()

# 查询学习内容
# 1）ORM查询基础
    def get_user_by_id(self,pk):
        # 根据用户ID查询用户信息
        # user_obj = self.session.get(User,pk)
        user_obj = self.session.get(User, {'id':pk})
        return user_obj
    def get_one(self):
        # 返回一条记录
        # stmt = select(User)
        # 以下两个返回的是元组
        # row = self.session.execute(stmt).fetchone()
        # row = self.session.execute(stmt).first()
        # scalars是把数据转换成字段
        # row = self.session.execute(stmt).scalars().first()

        stmt = select(User).where(User.id == 1)
        # row = self.session.execute(stmt).scalars().one()
        # row = self.session.execute(stmt).scalar_one()
        row = self.session.execute(stmt).scalar_one_or_none()
        return row

    def get_more(self):
        # 返回多条记录

        # 返回的Row
        # stmt = select(User)
        # stmt = select(User.id,User.username,User.real_name)
        # row = self.session.execute(stmt)
        # 返回字典
        # stmt = select(User.id, User.username, User.real_name)
        # row = self.session.execute(stmt).mappings()
        # 返回ORM实体的对象
        stmt = select(User)
        row = self.session.execute(stmt).scalars()

        return row


# 2）条件查询
#     以下使用的是student表来演示(use_sqlalchemy_orm_crud_oop_school)
# 3）函数的使用
#     以下使用的是student表来演示(use_sqlalchemy_orm_crud_oop_school)
# 4）多表连接
    def get_user_info(self):
        # 主外键关联的联系
        user_obj = self.session.get(User,2)
        print('用户',user_obj.real_name)
        addr_list = user_obj.addresses
        print("地址",addr_list) #打印出个sql？
        print("地址",type(addr_list))
        for item in addr_list:
            print(item.id,item.area,item.user_id)

        # 筛选
        new_addr_list = addr_list.filter(UserAddress.id < 3)
        print('筛选地址',new_addr_list,new_addr_list.count())
        print('筛选地址',new_addr_list.count())

        # 反向取内容
        addr_obj = self.session.get(UserAddress,1)
        print("地址",addr_obj.id,addr_obj.area)
        print(addr_obj.user)
# 5）分组与去重
# 6）排序
# 7）分页处理


    def close(self):
        try:
            self.session.close()
        except:
            pass

def main():
    obj = MySqlOrmTest()
    # obj.add_user()
    # obj.add_many_user()
    # obj.add_user_and_profile_address()
    # user_obj = obj.get_user_by_id(1)
    # user_obj = obj.get_one()
    # print(user_obj)

    # queryset = obj.get_more()
    # for item in queryset:
    #     print(item)
    #     print(item.username)

    obj.get_user_info()

    obj.close()

if __name__ == '__main__':
    main()